{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2509ee71-74ee-4f7e-9847-6dfd2b8d9850",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9c7bd907-6fa5-44f6-9a5f-fab2e3e714d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = 'C:/Users/new/Desktop/疫情演化系统/数据/Geocoding.csv'\n",
    "data = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ccd0e9db-1f78-4635-98ec-6be2825d5d89",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop(['原始文本', '来源', '风险等级', '存疑'], axis=1, inplace=True) # 去掉不需要的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4fa0cadd-efb7-491d-ad29-c8c2ed6106bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>病例号</th>\n",
       "      <th>起始日期</th>\n",
       "      <th>起始时间</th>\n",
       "      <th>结束日期</th>\n",
       "      <th>结束时间</th>\n",
       "      <th>场所</th>\n",
       "      <th>经度</th>\n",
       "      <th>纬度</th>\n",
       "      <th>完整地址</th>\n",
       "      <th>所在区</th>\n",
       "      <th>街道</th>\n",
       "      <th>township</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>14:01:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地铁6号线南翠屏站</td>\n",
       "      <td>117.158640</td>\n",
       "      <td>39.079964</td>\n",
       "      <td>天津市南开区体育中心街道南翠屏(地铁站)南翠屏公园</td>\n",
       "      <td>南开区</td>\n",
       "      <td>宾水西道</td>\n",
       "      <td>体育中心街道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>14:11:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地铁5号线肿瘤医院站</td>\n",
       "      <td>117.183701</td>\n",
       "      <td>39.080221</td>\n",
       "      <td>天津市河西区天塔街道天津市肿瘤医院</td>\n",
       "      <td>河西区</td>\n",
       "      <td>环湖西路</td>\n",
       "      <td>天塔街道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>14:21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地铁1号线下瓦房站</td>\n",
       "      <td>117.222795</td>\n",
       "      <td>39.140884</td>\n",
       "      <td>天津市河东区春华街道远洋天地新天地家园</td>\n",
       "      <td>河东区</td>\n",
       "      <td>华捷道</td>\n",
       "      <td>春华街道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>14:41:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地铁1号线财经大学站</td>\n",
       "      <td>117.278442</td>\n",
       "      <td>39.061219</td>\n",
       "      <td>天津市河西区尖山街道天津财经大学-教学楼K座</td>\n",
       "      <td>河西区</td>\n",
       "      <td>珠江道</td>\n",
       "      <td>尖山街道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>14:58:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>灰堆公交站</td>\n",
       "      <td>117.276909</td>\n",
       "      <td>39.071087</td>\n",
       "      <td>天津市河西区柳林街道大沽南路辅路</td>\n",
       "      <td>河西区</td>\n",
       "      <td>大沽南路</td>\n",
       "      <td>柳林街道</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   病例号        起始日期      起始时间 结束日期 结束时间          场所          经度         纬度  \\\n",
       "0    1  2021-12-23  14:01:00  NaN  NaN   地铁6号线南翠屏站  117.158640  39.079964   \n",
       "1    1  2021-12-23  14:11:00  NaN  NaN  地铁5号线肿瘤医院站  117.183701  39.080221   \n",
       "2    1  2021-12-23  14:21:00  NaN  NaN   地铁1号线下瓦房站  117.222795  39.140884   \n",
       "3    1  2021-12-23  14:41:00  NaN  NaN  地铁1号线财经大学站  117.278442  39.061219   \n",
       "4    1  2021-12-23  14:58:00  NaN  NaN       灰堆公交站  117.276909  39.071087   \n",
       "\n",
       "                        完整地址  所在区    街道 township  \n",
       "0  天津市南开区体育中心街道南翠屏(地铁站)南翠屏公园  南开区  宾水西道   体育中心街道  \n",
       "1          天津市河西区天塔街道天津市肿瘤医院  河西区  环湖西路     天塔街道  \n",
       "2        天津市河东区春华街道远洋天地新天地家园  河东区   华捷道     春华街道  \n",
       "3     天津市河西区尖山街道天津财经大学-教学楼K座  河西区   珠江道     尖山街道  \n",
       "4           天津市河西区柳林街道大沽南路辅路  河西区  大沽南路     柳林街道  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7d65fb3e-180e-4e91-aac8-c2df896e4423",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 找出所有区\n",
    "region_set = set()\n",
    "for index,row in data.iterrows():\n",
    "    if row['township'] is not np.NaN:\n",
    "        region_set.add(row['township'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "83373f87-b24b-4912-b0e8-9bf83bedd4ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'丁字沽街道',\n",
       " '万兴街道',\n",
       " '万新街道',\n",
       " '三条石街道',\n",
       " '上杭路街道',\n",
       " '下朱庄街道',\n",
       " '下瓦房街道',\n",
       " '东丽区开发区',\n",
       " '东新街道',\n",
       " '东蒲洼街道',\n",
       " '东马圈镇',\n",
       " '中北镇',\n",
       " '中塘镇',\n",
       " '二号桥街道',\n",
       " '五大道街道',\n",
       " '体育中心街道',\n",
       " '佳荣里街道',\n",
       " '光复道街道',\n",
       " '八里台镇',\n",
       " '兴南街道',\n",
       " '军粮城街道',\n",
       " '劝业场街道',\n",
       " '北仓镇',\n",
       " '北塘经济区',\n",
       " '北淮淀镇',\n",
       " '北闸口镇',\n",
       " '华新街道',\n",
       " '华明街道',\n",
       " '华苑街道',\n",
       " '南市街道',\n",
       " '南营门街道',\n",
       " '南蔡村镇',\n",
       " '友谊路街道',\n",
       " '双口镇',\n",
       " '双林农场',\n",
       " '双桥河镇',\n",
       " '双港镇',\n",
       " '双街镇',\n",
       " '古林街道',\n",
       " '向阳楼街道',\n",
       " '向阳路街道',\n",
       " '咸水沽镇',\n",
       " '咸阳北路街道',\n",
       " '嘉陵道街道',\n",
       " '塘沽街道',\n",
       " '大寺镇',\n",
       " '大沽街道',\n",
       " '大港街道',\n",
       " '大王庄街道',\n",
       " '大直沽街道',\n",
       " '大良镇',\n",
       " '大营门街道',\n",
       " '天塔街道',\n",
       " '天宝工业园区',\n",
       " '天津开发区西区',\n",
       " '天津滨海新区高新技术产业开发区',\n",
       " '天津空港经济区',\n",
       " '天穆镇',\n",
       " '太平镇',\n",
       " '学府街道',\n",
       " '宁园街道',\n",
       " '宜兴埠镇',\n",
       " '宝平街道',\n",
       " '富民路街道',\n",
       " '寨上街道',\n",
       " '小王庄镇',\n",
       " '小白楼街道',\n",
       " '小站镇',\n",
       " '尖山街道',\n",
       " '州河湾镇',\n",
       " '常州道街道',\n",
       " '广开街道',\n",
       " '建昌道街道',\n",
       " '张家窝镇',\n",
       " '挂甲寺街道',\n",
       " '新兴街道',\n",
       " '新北街道',\n",
       " '新城镇',\n",
       " '新开河街道',\n",
       " '新技术产业园区',\n",
       " '新村街道',\n",
       " '新河街道',\n",
       " '新港街道',\n",
       " '新立街道',\n",
       " '无瑕街道',\n",
       " '春华街道',\n",
       " '普东街道',\n",
       " '月牙河街道',\n",
       " '望海楼街道',\n",
       " '李七庄街道',\n",
       " '杨柳青镇',\n",
       " '杭州道街道',\n",
       " '林亭口镇',\n",
       " '果园新村街道',\n",
       " '柳林街道',\n",
       " '桃园街道',\n",
       " '梅江街道',\n",
       " '武清区开发区',\n",
       " '武清汽车园',\n",
       " '水上公园街道',\n",
       " '汉沽街道',\n",
       " '汊沽港镇',\n",
       " '江都路街道',\n",
       " '沿庄镇',\n",
       " '泰达街道',\n",
       " '津南开发区东区',\n",
       " '津南开发区西区',\n",
       " '海北镇',\n",
       " '海河教育园区',\n",
       " '海滨街道',\n",
       " '渔阳镇',\n",
       " '滨海旅游区',\n",
       " '潘庄镇',\n",
       " '独流镇',\n",
       " '王串场街道',\n",
       " '王口镇',\n",
       " '王庆坨镇',\n",
       " '王稳庄镇',\n",
       " '王顶堤街道',\n",
       " '瑞景街道',\n",
       " '石各庄镇',\n",
       " '礼明庄镇',\n",
       " '精武镇',\n",
       " '罗庄子镇',\n",
       " '胡家园街道',\n",
       " '葛沽镇',\n",
       " '蔡公庄镇',\n",
       " '西于庄街道',\n",
       " '西沽街道',\n",
       " '西营门街道',\n",
       " '西青区开发区',\n",
       " '越秀路街道',\n",
       " '辛庄镇',\n",
       " '运河西街道',\n",
       " '造甲城镇',\n",
       " '邦均镇',\n",
       " '邵公庄街道',\n",
       " '金桥街道',\n",
       " '金钟街道',\n",
       " '铁东路街道',\n",
       " '铁路十八局',\n",
       " '铃铛阁街道',\n",
       " '长虹街道',\n",
       " '陈咀镇',\n",
       " '陈塘庄街道',\n",
       " '陈官屯镇',\n",
       " '集贤里街道',\n",
       " '青光镇',\n",
       " '静海镇',\n",
       " '马场街道',\n",
       " '鸿顺里街道',\n",
       " '鼓楼街道'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region_set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e7952b55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "152"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(region_set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fd9fb944-cf4b-4590-8b59-a3cab9d99bf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 生成时间序列\n",
    "start_timestamp = pd.Timestamp(2021,12,23)\n",
    "end_timestamp = pd.Timestamp(2022,2,1)\n",
    "time_series = pd.date_range(start_timestamp,end_timestamp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "585588fb-5e13-4e61-8459-0df390d9236e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2021-12-23', '2021-12-24', '2021-12-25', '2021-12-26',\n",
       "               '2021-12-27', '2021-12-28', '2021-12-29', '2021-12-30',\n",
       "               '2021-12-31', '2022-01-01', '2022-01-02', '2022-01-03',\n",
       "               '2022-01-04', '2022-01-05', '2022-01-06', '2022-01-07',\n",
       "               '2022-01-08', '2022-01-09', '2022-01-10', '2022-01-11',\n",
       "               '2022-01-12', '2022-01-13', '2022-01-14', '2022-01-15',\n",
       "               '2022-01-16', '2022-01-17', '2022-01-18', '2022-01-19',\n",
       "               '2022-01-20', '2022-01-21', '2022-01-22', '2022-01-23',\n",
       "               '2022-01-24', '2022-01-25', '2022-01-26', '2022-01-27',\n",
       "               '2022-01-28', '2022-01-29', '2022-01-30', '2022-01-31',\n",
       "               '2022-02-01'],\n",
       "              dtype='datetime64[ns]', freq='D')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0945d867-149c-43bc-bcf2-4a0a8ccfb81b",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X = pd.DataFrame(columns=('日期', '所在街道', '经停病例数'))\n",
    "length = len(train_X.index)\n",
    "for place in region_set:\n",
    "    for time in time_series:\n",
    "        train_X.loc[length] = [time, place, 0]\n",
    "        length += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0f5a81f2-0f3f-4f38-b15b-f00699bc0595",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>所在街道</th>\n",
       "      <th>经停病例数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>东马圈镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>东马圈镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-12-25</td>\n",
       "      <td>东马圈镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-12-26</td>\n",
       "      <td>东马圈镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-12-27</td>\n",
       "      <td>东马圈镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6227</th>\n",
       "      <td>2022-01-28</td>\n",
       "      <td>北仓镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6228</th>\n",
       "      <td>2022-01-29</td>\n",
       "      <td>北仓镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6229</th>\n",
       "      <td>2022-01-30</td>\n",
       "      <td>北仓镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6230</th>\n",
       "      <td>2022-01-31</td>\n",
       "      <td>北仓镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6231</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>北仓镇</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6232 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期  所在街道  经停病例数\n",
       "0    2021-12-23  东马圈镇      0\n",
       "1    2021-12-24  东马圈镇      0\n",
       "2    2021-12-25  东马圈镇      0\n",
       "3    2021-12-26  东马圈镇      0\n",
       "4    2021-12-27  东马圈镇      0\n",
       "...         ...   ...    ...\n",
       "6227 2022-01-28   北仓镇      0\n",
       "6228 2022-01-29   北仓镇      0\n",
       "6229 2022-01-30   北仓镇      0\n",
       "6230 2022-01-31   北仓镇      0\n",
       "6231 2022-02-01   北仓镇      0\n",
       "\n",
       "[6232 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_X"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6538322c-89c0-4a29-b148-77e92c58ae46",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index,row in data.iterrows():\n",
    "    startdatetime = row['起始日期']\n",
    "    region = row['township']\n",
    "    rows_index = train_X[(train_X['日期'] == startdatetime) & (train_X['所在街道'] == region)].index.tolist()\n",
    "    if rows_index:\n",
    "        for r in rows_index:\n",
    "            train_X.loc[r, '经停病例数'] += 1\n",
    "            # print(train_X.loc[r])\n",
    "    # else:\n",
    "        # print(\"error\")\n",
    "        # train_X.loc[len(train_X.index)] = [datetime.fromtimestamp(startdatetime.timestamp()), place, 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c217ec73-ccae-437c-b690-8ef15e4f74cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_path='C:/Users/new/Desktop/train_X.csv'\n",
    "train_X.to_csv(output_path,sep=',',index=False,header=False,encoding='utf_8_sig')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76434ac1-4510-4f71-afe1-9755f7298688",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
